package com.daatak.finance.dao;

import java.math.BigDecimal;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.daatak.finance.domain.FinancialProductObject;
import com.daatak.finance.domain.RateBandObject;
import com.daatak.finance.domain.RateCardObject;

@Repository
@Transactional
public class FinancialsDao implements FinancialsDaoI {
	
	String rateBandByAmountTerm = "select rb.* from tmp_rate_bands rb, tmp_rate_cards rc "
				+ "where rc.FINANCIAL_PRODUCT_ID = :fpId "
				+ "and rc.MAX_AMOUNT>=25000 and rc.MIN_AMOUNT<=:amount "
				+ "and rb.RATE_CARD_ID = rc.RATE_CARD_ID "
				+ "and rb.MAX_TERM>=:term and rb.MIN_TERM<=:term";
				
	      
	@Autowired
	 SessionFactory sessionFactory;
	 
	 public void setSessionFactory(SessionFactory sessionFactory) {
		 this.sessionFactory=sessionFactory;
	    }
	public void createRateCard(RateCardObject rateCard) {
		getSession().saveOrUpdate(rateCard);
		
	}
	public List<RateCardObject> getRateCardList() {
		// TODO Auto-generated method stub
		return null;
	}
	public List<RateBandObject> getRateBandByRateCardId(Integer rateCardId) {
		Criteria criteria  = getSession().createCriteria(RateBandObject.class);
		return criteria.add(Restrictions.eq("rateCardId", rateCardId)).list();
		
	}
	public void createRateBand(RateBandObject rateBand) {
		getSession().saveOrUpdate(rateBand);
		
	}
	public String createFinancialProduct(FinancialProductObject financialProduct) {
		getSession().saveOrUpdate(financialProduct);
		return "";
				
	}
	public RateCardObject getRateCardById(Integer rateCardId) {
		// TODO Auto-generated method stub
		return null;
	}
	public FinancialProductObject loadFinancialProduct(Integer id) {
		// TODO Auto-generated method stub
		return null;
	}
	public List<RateBandObject> listRateBand() {
		// TODO Auto-generated method stub
		return null;
	}
	public int assignRateBandToRateCard(String[] rateCardId, String[] rateBandId) {
		// TODO Auto-generated method stub
		return 0;
	}
	public FinancialProductObject findById(Integer id) {
		// TODO Auto-generated method stub
		return (FinancialProductObject)getSession().get(FinancialProductObject.class, id);
	}
	public RateCardObject rateCardById(Integer rateCardId) {
		// TODO Auto-generated method stub
		return null;
	}
	public List<RateCardObject> listRateCards(Integer financialProductId) {
		// TODO  check whether the Criteria is best or not for selection
		Criteria criteria  = getSession().createCriteria(RateCardObject.class);
		return criteria.add(Restrictions.eq("financialProductId", financialProductId)).list();
		
		
		
	}
	public List<RateBandObject> getRateBands(Integer financialProductId, BigDecimal amount, Integer term) {
		// TODO  check whether the Criteria is best or not for selection
		
		Query query  = getSession().createSQLQuery(rateBandByAmountTerm).addEntity(RateBandObject.class);
		
		query.setBigDecimal("amount", amount);
		query.setInteger("fpId", financialProductId);
		query.setInteger("term", term);
		return query.list();
		
	}
	public List<RateBandObject> listRateBand(Integer rateCardId,
			Integer financialProductId) {
		// TODO Auto-generated method stub
		return null;
	}
	public List<FinancialProductObject> list() {
		// TODO Auto-generated method stub
		
		return getSession().createQuery("from FinancialProductObject").list();
		
	}
	
	public Session getSession(){
		return sessionFactory.getCurrentSession();
	}
	@Override
	public void create(RateBandObject rateBandObject) {
		getSession().saveOrUpdate(rateBandObject);
		
	}
	@Override
	public void deleteRate(Integer id) {
		getSession().delete(getSession().get(RateCardObject.class,id));
		
	}
	@Override
	public void deleteRateBand(Integer id) {
		// TODO Auto-generated method stub
		getSession().delete(getSession().get(RateBandObject.class,id));
		
	}
	@Override
	public List<RateCardObject> getRateCard(Integer finacialProductId,
			BigDecimal financedAmount) {
		// TODO Auto-generated method stub
		Criteria criteria  = getSession().createCriteria(RateCardObject.class);
		criteria.add(Restrictions.eq("financialProductId", finacialProductId));
		criteria.add(Restrictions.and(Restrictions.ge("maxAmount",financedAmount)));
		criteria.add(Restrictions.and(Restrictions.le("minAmount",financedAmount)));
		return criteria.list();
	}
	@Override
	public List<RateBandObject> getRateBand(Integer rateCardId, Integer term) {
		Criteria criteria  = getSession().createCriteria(RateBandObject.class);
		criteria.add(Restrictions.eq("rateCardId", rateCardId));
		criteria.add(Restrictions.and(Restrictions.ge("maxTerm",term)));
		criteria.add(Restrictions.and(Restrictions.le("minTerm",term)));
		return criteria.list();
	}
			
		    
	   
	    
}
